#####################################################################
# Product:      KnowledgeEngine (Plone Edition)
#
# File:         MySQLCreditManagementService.py
#
# Copyright:    Copyright (c) 2004, Faulkner Technologies
#
# Author:       Brian Oliver
#
# Description:  Defines a security service,
#               implementation based on the MySQL database
#               (using InnoDB tables)
#
#               NOTE: sql commands to create the appropriate
#               mysql database for this service
#               are located in the folder containing this file.
#
# See Also:     SecurityService, Permission
#####################################################################

#####################################################################
## KnowledgeEngine Library Imports
from Products.KnowledgeEngine.Common import *
from Products.KnowledgeEngine.Core import *
from Products.KnowledgeEngine.Permission import *
from Products.KnowledgeEngine.CreditManagementService import CreditManagementServiceInfo, CreditManagementService

#####################################################################
## Zope Library Imports
from Globals import DTMLFile
from App.ImageFile import ImageFile
from DateTime import DateTime
 
#####################################################################
## Python Library Imports
# (None)

#---------------------------------------------------------------#

#MYSQL Constants
DATETIME_FORMAT = "%Y-%m-%d %H:%M:%S"

#####################################################################
## MySQLCreditManagementServiceInfo Class Definition
class MySQLCreditManagementServiceInfo(CreditManagementServiceInfo):
    """
        Defines the security service meta-information
    """
    
    #---------------------------------------------------------------#
    
    def getClassName(self):
        """
            Returns the name of the class for which 
            we are providing information.
        """
        return 'MySQLCreditManagementService'


    #---------------------------------------------------------------#

    def getDisplayName(self):
        """
            Return a friendly name for the class instances
            (this is used for displaying in lists etc)
        """
    
        return "KnowledgeEngine: MySQL Credit Management Service"

#####################################################################
## MySQLCreditManagementService Class Definition
class MySQLCreditManagementService(CreditManagementService):
    """
        Defines a tool that may be used to manage Knowledge Engine
        security
    """

    #---------------------------------------------------------------#

    #ABSTRACT TRANSIENT: a ServiceInfo implementation instance to provide info about this service
    _SERVICEINFO = MySQLCreditManagementServiceInfo()

    #---------------------------------------------------------------#

    def __init__(self):
        """
            Constructor for the object
        """
        
        #perform default initialisations
        CreditManagementService.__init__(self)
        
        #create the default properties (just the name of the mysql connection object)
        self._setProperty('dbConnectionName', '', 'string')

    #---------------------------------------------------------------#

    def getDatabaseConnection(self):
        """
            PRIVATE: Return a Zope Data Adapter (Connection object) to be
            used by this security service
        """

        #attempt to locate the Zope Database Connection 
        zda = getattr(self, self.dbConnectionName)

        #get and return a real connection from the zda
        return zda()
        
    #---------------------------------------------------------------#
        
    def getAvailableCredits(self, permissionIdentity, securableIdentity, actorIdentity):
        """
            Returns the number of currently available credits for the specified
            permission, securable, actor tuple.

            RETURN TYPE: Integer
        """            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #the sum query
        sql = "SELECT SUM(amount) FROM credits WHERE permissionIdentity = '%s' AND securableIdentity = '%s' AND actorIdentity = '%s'" % (permissionIdentity, securableIdentity, actorIdentity)
        (columns, resultset) = dbConnection.query(sql)
        
        #get the resulting row
        row = resultset[0]

        #was there a result (mysql will return NULL/None if no rows are found when summing)
        if row[0] is None:
            return 0
        else:
            return int(row[0])
        
    #---------------------------------------------------------------#
        
    def depositCredits(self, permissionIdentity, securableIdentity, actorIdentity, createdBy, amount, narration = None):
        """
            Deposits the specified 'amount' of credits in the 'account' for the
            specified permission, securable and actor tuple.
            
            PARAMETERS:
                createdBy - used to track who is making the transaction (often the same as the actorIdentity, but not necessarily so)
                narration - free text comments about the transaction (typically not used)
            
            PRE-CONDITION:
                amount > 0
            
            RETURN TYPE: String (containing the creditTransactionIdentity that was generated for the transaction)            
        """            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #get a new credit transaction identity
        creditTxn = generateIdentity("credittxn")

        #ensure narration is empty
        if narration is None:
            narration = ""

        #execute the deposit
        sql = "INSERT INTO credits (creditTransactionIdentity, permissionIdentity, securableIdentity, actorIdentity, instantCreated, createdBy, amount, narration) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d, '%s')" % (creditTxn, permissionIdentity, securableIdentity, actorIdentity, DateTime().strftime(DATETIME_FORMAT), createdBy, amount, narration)
        dbConnection.query(sql)
        
        #we're done...
        return creditTxn
        
    #---------------------------------------------------------------#
        
    def withdrawCredits(self, permissionIdentity, securableIdentity, actorIdentity, createdBy, amount, narration = None):
        """
            Withdraws the specified 'amount' of credits in the 'account' for the
            specified permission, securable and actor tuple.
            
            PARAMETERS:
                createdBy - used to track who is making the transaction (often the same as the actorIdentity, but not necessarily so)
                narration - free text comments about the transaction (typically not used)
            
            PRE-CONDITION:
                amount > 0

            NOTE: Allows 'accounts' to be OVERDRAWN (ie: getAvailableCredits may return < 0)
            
            RETURN TYPE: String (containing the creditTransactionIdentity that was generated for the transaction)            
        """            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #get a new credit transaction identity
        creditTxn = generateIdentity("credittxn")

        #ensure narration is empty
        if narration is None:
            narration = ""

        #execute the deposit
        sql = "INSERT INTO credits (creditTransactionIdentity, permissionIdentity, securableIdentity, actorIdentity, instantCreated, createdBy, amount, narration) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d, '%s')" % (creditTxn, permissionIdentity, securableIdentity, actorIdentity, DateTime().strftime(DATETIME_FORMAT), createdBy, -amount, narration)
        dbConnection.query(sql)
        
        #we're done...
        return creditTxn
        
    #---------------------------------------------------------------#
        
    def reverseCredits(self, creditTransactionIdentity, createdBy, narration = None):
        """
            ABSTRACT: Reverses a previously executed credit transaction (by posting an
            appropriate transaction for the -amount of the specified transaction)
            
            PARAMETERS:
                createdBy - used to track who is making the transaction
                narration - free text comments about the transaction (typically not used)
            
            RETURN TYPE: String (containing the creditTransactionIdentity that was generated for the transaction or None if not successful)            
        """            
        
        #get a database connection to work with
        dbConnection = self.getDatabaseConnection()

        #attempt to find the specified transaction 
        sql = "SELECT permissionIdentity, securableIdentity, actorIdentity, amount FROM credits WHERE creditTransactionIdentity = '%s'" % (creditTransactionIdentity, )
        (columns, resultset) = dbConnection.query(sql)
        
        #did we find the credit transaction?
        if len(resultset) > 0:
            #get the credit transaction row
            values = resultset[0]
        
            #get the amount
            amount = values[3]
            
            #determine if we should do a deposit or withdraw to reverse the transaction?
            if amount < 0:
                #deposit
                amount = -amount
                return self.depositCredits(values[0], values[1], values[2], createdBy, amount, narration)
                
            else:
                #withdraw
                return self.withdrawCredits(values[0], values[1], values[2], createdBy, amount, narration)
        
        else:
            #the specified transaction doesn't exist
            return None
            
    #---------------------------------------------------------------#
        
#####################################################################
## Class Initialisation
# (none)

# Register Service Information with the Knowledge Engine
registerServiceClass(MySQLCreditManagementService)

#####################################################################
